﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevComponents.DotNetBar;
using Cocbook_DTO;
using Cocbook_DAL;
using System.Data.OleDb;
using System.IO;

namespace Sale_and_manage
{
    public partial class frmBookInfoExport : DevComponents.DotNetBar.Metro.MetroForm
    {
        public frmBookInfoExport()
        {
            InitializeComponent();
        }

        List<Book> lb = new List<Book>();

        private void btnGetFilelocation_Click(object sender, EventArgs e)
        {

        }

        public void ExportFileExcel(string FileLocation)
        {
            string fileLocation = saveFileDialog1.FileName;
            OleDbConnection connection = new OleDbConnection();
            File.Copy(@"BookInfomation.xls", fileLocation, true);

            connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties = \"Excel 8.0;HDR=Yes;\";");
            OleDbCommand cmd;
            string excelQuery = @"INSERT INTO [Sheet1$]([TT], [ISBN], [Title], [Type], [Publisher], [Author], [Size], [Page], [Price], [Issuer], [Genre], [Weigh]) " +
                                "VALUES (@tt, @isbn, @title, @type, @publisher, @author, @size, @page, @price, @issuer, @genre, @weigh)";;
            connection.Open();
            progressBar1.Maximum = lb.Count-1;
            progressBar1.Minimum = 0;
            int status = 0;
            for (int i = 0; i < lb.Count; i++)
            {
                cmd = new OleDbCommand(excelQuery, connection);
                cmd.Parameters.AddWithValue("tt", (i+1).ToString());
                cmd.Parameters.AddWithValue("isbn", lb[i].ISBN.ToString());
                cmd.Parameters.AddWithValue("title", lb[i].Title.ToString());
                cmd.Parameters.AddWithValue("type", lb[i].Type.ToString());
                cmd.Parameters.AddWithValue("publisher", lb[i].Publisher.ToString());
                cmd.Parameters.AddWithValue("issuer", lb[i].Issuer.ToString());
                cmd.Parameters.AddWithValue("size", lb[i].Size.ToString());
                cmd.Parameters.AddWithValue("page", lb[i].Page.ToString());
                cmd.Parameters.AddWithValue("price", lb[i].Price.ToString());
                cmd.Parameters.AddWithValue("genre", lb[i].Genre.ToString());
                cmd.Parameters.AddWithValue("weigh", lb[i].Weigh.ToString());
                Author author = (new AuthorDAL()).GetAuthorByBookID(lb[i].BookID);
                if (author != null)
                {
                    cmd.Parameters.AddWithValue("author", author.Name);
                }
                else
                {
                    cmd.Parameters.AddWithValue("author", "N/A");
                }
                cmd.ExecuteNonQuery();
                progressBar1.Value = i;
                if (i * 100 / lb.Count > status)
                {
                    status = i * 100 / lb.Count;
                    lblStatus.Text = status.ToString() + "%";
                    lblStatus.Refresh();
                }
            }
            connection.Close();
            MessageBox.Show("Hoàn tất");
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            saveFileDialog1.Title = "Save as...";
            saveFileDialog1.FileName = "Cocbook - Báo cáo kho";
            saveFileDialog1.Filter = "Excel 93-2003 Workbook|*.xls";
            if (saveFileDialog1.ShowDialog() != DialogResult.Cancel)
            {
                btnExport.Enabled = false;
                btnRefresh.Enabled = false;
                ExportFileExcel(saveFileDialog1.FileName);
                btnExport.Enabled = !false;
                btnRefresh.Enabled = !false;
            }
        }

        public void LoadDatatable()
        {
            lb = (new BookDAL()).GetAllBook();
            dgvBookExport.DataSource = lb;
            dgvBookExport.Columns[0].Visible = false;
            dgvBookExport.Columns[1].Width = 90;
            dgvBookExport.Columns[2].Width = 260;
            dgvBookExport.Columns[3].Width = 40;
            dgvBookExport.Columns[4].Width = 130;
            dgvBookExport.Columns[5].Width = 130;
            dgvBookExport.Columns[6].Width = 50;
            dgvBookExport.Columns[7].Width = 40;
            dgvBookExport.Columns[8].Width = 50;
            dgvBookExport.Columns[9].Width = 130;
            dgvBookExport.Columns[10].Width = 50;


            dgvBookExport.Columns[1].Width = 90;
            dgvBookExport.Columns[1].Width = 90;
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            LoadDatatable();
        }

        private void frmBookReport_Load(object sender, EventArgs e)
        {
            LoadDatatable();
        }
    }
}